﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;

namespace BLL
{
    public class DonDatHangBUO:IBUO<tbl_DonDatHang>
    {
        CSDLBanHangDataContext context = new CSDLBanHangDataContext();

        /// <summary>
        /// chèn vào bảng tbl_DonDathang
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public bool Insert(tbl_DonDatHang obj)
        {
            try
            {
                context.tbl_DonDatHangs.InsertOnSubmit(obj);
                context.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// insert vao bang tbl_DDH_Hang
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool InsertDDH_Hang(tbl_DDH_Hang obj)
        {
            try
            {
                context.tbl_DDH_Hangs.InsertOnSubmit(obj);
                context.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// lay ngay dat cua ddh theo id_ddh
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public IQueryable GetNgayDat(String id)
        {
            var list = context.tbl_DonDatHangs.Where(p => p.id_DonDatHang.Equals(id));
            return list;
        }

        public bool Delete(String id)
        {
            try
            {
                tbl_DonDatHang obj = context.tbl_DonDatHangs.Where(p => p.id_DonDatHang.Equals(id)).FirstOrDefault();
                context.tbl_DonDatHangs.DeleteOnSubmit(obj);
                context.SubmitChanges();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// Delete trong UC Them va Sua DDH
        /// </summary>
        /// <param name="idDDH"></param>
        /// <param name="idHang"></param>
        /// <returns></returns>
        public bool DeleteChiTietDDH(String idDDH, String idHang)
        {
            try
            {
                tbl_DDH_Hang obj = context.tbl_DDH_Hangs.Where(p => p.id_DonDatHang.Equals(idDDH) && p.id_Hang.Equals(idHang)).FirstOrDefault();
                context.tbl_DDH_Hangs.DeleteOnSubmit(obj);
                context.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool Delete(tbl_DonDatHang obj)
        {
            throw new NotImplementedException();
        }

        public List<tbl_DonDatHang> GetAll()
        {
            return context.tbl_DonDatHangs.OrderByDescending(p => p.id_DonDatHang).ToList();
        }

        public tbl_DonDatHang GetSingle(String id)
        {
            return context.tbl_DonDatHangs.Where(p => p.id_DonDatHang.Equals(id)).FirstOrDefault();
        }

        public bool Update(String id, tbl_DonDatHang obj)
        {
            tbl_DonDatHang ddh = context.tbl_DonDatHangs.Where(p => p.id_DonDatHang == id.Trim()).FirstOrDefault();
            ddh.NgayGiao = obj.NgayGiao;

            context.SubmitChanges();
            return true;
        }

        public bool UpdateDDH_Hang(String idDDH, String idHang, tbl_DDH_Hang obj)
        {
            try
            {
                tbl_DDH_Hang ddhh = context.tbl_DDH_Hangs.Where(p => p.id_DonDatHang.Equals(idDDH) && p.id_Hang.Equals(idHang)).FirstOrDefault();
                ddhh.SoLuong = obj.SoLuong;

                context.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public String GetNewIDDDH()
        {
            return context.tbl_DonDatHangs.OrderByDescending(p => p.id_DonDatHang).Select(n => n.id_DonDatHang).FirstOrDefault();
        }

        /// <summary>
        /// tìm theo ngày đặt hàng
        /// </summary>
        /// <param name="ngay"></param>
        /// <returns></returns>
        public IQueryable searchByNDH(DateTime ngay)
        {
            DateTime ndh = (DateTime)ngay;

            var list = context.tbl_NhaCungCaps.Join(
                    context.tbl_NCC_Hangs,
                    a => a.id_NhaCungCap,
                    b => b.id_NhaCungCap,
                    (a, b) => new
                    {
                        id_NhaCungCap = a.id_NhaCungCap,
                        TenNhaCungCap = a.TenNhaCungCap,
                        id_Hang = b.id_Hang
                    }
                ).Join(
                    context.tbl_Hangs,
                    c => c.id_Hang,
                    d => d.id_Hang,
                    (c, d) => new
                    {
                        id_Hang = c.id_Hang,
                        TenNhaCungCap = c.TenNhaCungCap
                    }
                ).Join(
                    context.tbl_DDH_Hangs,
                    g => g.id_Hang,
                    h => h.id_Hang,
                    (g, h) => new
                    {
                        id_Hang = g.id_Hang,
                        id_DonDatHang = h.id_DonDatHang,
                        TenNhaCungCap = g.TenNhaCungCap
                    }
                ).Join(
                    context.tbl_DonDatHangs,
                    i => i.id_DonDatHang,
                    j => j.id_DonDatHang,
                    (i, j) => new
                    {
                        id_DonDatHang = i.id_DonDatHang,
                        id_NhanVien = j.id_NhanVien,
                        NgayDat = j.NgayDat,
                        NgayGiao = j.NgayGiao,
                        TenNhaCungCap = i.TenNhaCungCap
                    }
                ).Join(
                    context.tbl_NhanViens,
                    l => l.id_NhanVien,
                    m => m.id_NhanVien,
                    (l, m) => new
                    {
                        id_NhanVien = l.id_NhanVien,
                        TenNhanVien = m.TenNhanVien,
                        id_DonDatHang = l.id_DonDatHang,
                        TenNhaCungCap = l.TenNhaCungCap,
                        NgayDat = l.NgayDat,
                        NgayGiao = l.NgayGiao
                    }
                ).Where(p => DateTime.Compare((DateTime)p.NgayDat, ndh) == 0);
            return list;
        }

        /// <summary>
        /// tìm theo tên nhân viên của đơn đặt hàng
        /// </summary>
        /// <param name="ten"></param>
        /// <returns></returns>
        public IQueryable searchByTenNV(String ten)
        {
            var list = context.tbl_NhaCungCaps.Join(
                    context.tbl_NCC_Hangs,
                    a => a.id_NhaCungCap,
                    b => b.id_NhaCungCap,
                    (a, b) => new
                    {
                        id_NhaCungCap = a.id_NhaCungCap,
                        TenNhaCungCap = a.TenNhaCungCap,
                        id_Hang = b.id_Hang
                    }
                ).Join(
                    context.tbl_Hangs,
                    c => c.id_Hang,
                    d => d.id_Hang,
                    (c, d) => new
                    {
                        id_Hang = c.id_Hang,
                        TenNhaCungCap = c.TenNhaCungCap
                    }
                ).Join(
                    context.tbl_DDH_Hangs,
                    g => g.id_Hang,
                    h => h.id_Hang,
                    (g, h) => new
                    {
                        id_Hang = g.id_Hang,
                        id_DonDatHang = h.id_DonDatHang,
                        TenNhaCungCap = g.TenNhaCungCap
                    }
                ).Join(
                    context.tbl_DonDatHangs,
                    i => i.id_DonDatHang,
                    j => j.id_DonDatHang,
                    (i, j) => new
                    {
                        id_DonDatHang = i.id_DonDatHang,
                        id_NhanVien = j.id_NhanVien,
                        NgayDat = j.NgayDat,
                        NgayGiao = j.NgayGiao,
                        TenNhaCungCap = i.TenNhaCungCap
                    }
                ).Join(
                    context.tbl_NhanViens,
                    l => l.id_NhanVien,
                    m => m.id_NhanVien,
                    (l, m) => new
                    {
                        id_NhanVien = l.id_NhanVien,
                        TenNhanVien = m.TenNhanVien,
                        id_DonDatHang = l.id_DonDatHang,
                        TenNhaCungCap = l.TenNhaCungCap,
                        NgayDat = l.NgayDat,
                        NgayGiao = l.NgayGiao
                    }
                ).Where(p => p.TenNhanVien.Contains(ten));
            return list;
        }

        /// <summary>
        /// tìm theo mã đơn đặt hàng
        /// </summary>
        /// <param name="ma"></param>
        /// <returns></returns>
        public IQueryable searchByMaDDH(String ma)
        {
            var list = context.tbl_NhaCungCaps.Join(
                    context.tbl_NCC_Hangs,
                    a => a.id_NhaCungCap,
                    b => b.id_NhaCungCap,
                    (a, b) => new
                    {
                        id_NhaCungCap = a.id_NhaCungCap,
                        TenNhaCungCap = a.TenNhaCungCap,
                        id_Hang = b.id_Hang
                    }
                ).Join(
                    context.tbl_Hangs,
                    c => c.id_Hang,
                    d => d.id_Hang,
                    (c, d) => new
                    {
                        id_Hang = c.id_Hang,
                        TenNhaCungCap = c.TenNhaCungCap
                    }
                ).Join(
                    context.tbl_DDH_Hangs,
                    g => g.id_Hang,
                    h => h.id_Hang,
                    (g, h) => new
                    {
                        id_Hang = g.id_Hang,
                        id_DonDatHang = h.id_DonDatHang,
                        TenNhaCungCap = g.TenNhaCungCap
                    }
                ).Join(
                    context.tbl_DonDatHangs,
                    i => i.id_DonDatHang,
                    j => j.id_DonDatHang,
                    (i, j) => new
                    {
                        id_DonDatHang = i.id_DonDatHang,
                        id_NhanVien = j.id_NhanVien,
                        NgayDat = j.NgayDat,
                        NgayGiao = j.NgayGiao,
                        TenNhaCungCap = i.TenNhaCungCap
                    }
                ).Join(
                    context.tbl_NhanViens,
                    l => l.id_NhanVien,
                    m => m.id_NhanVien,
                    (l, m) => new
                    {
                        id_NhanVien = l.id_NhanVien,
                        TenNhanVien = m.TenNhanVien,
                        id_DonDatHang = l.id_DonDatHang,
                        TenNhaCungCap = l.TenNhaCungCap,
                        NgayDat = l.NgayDat,
                        NgayGiao = l.NgayGiao
                    }
                ).Where(p => p.id_DonDatHang.Contains(ma.Trim()));
            return list;
        }

        /// <summary>
        /// Hiển thị danh sách các trường hiển thị trên datagridview, form DanhSachDDDH 
        /// </summary>
        /// <returns></returns>
        public IQueryable HienThiDanhSachDDH()
        {
            var list = context.tbl_NhaCungCaps.Join(
                    context.tbl_NCC_Hangs,
                    a => a.id_NhaCungCap,
                    b => b.id_NhaCungCap,
                    (a, b) => new
                    {
                        id_NhaCungCap = a.id_NhaCungCap,
                        TenNhaCungCap = a.TenNhaCungCap,
                        id_Hang = b.id_Hang
                    }
                ).Join(
                    context.tbl_Hangs,
                    c => c.id_Hang,
                    d => d.id_Hang,
                    (c, d) => new
                    {
                        id_NhaCungCap = c.id_NhaCungCap,
                        id_Hang = c.id_Hang,
                        TenNhaCungCap = c.TenNhaCungCap
                    }
                ).Join(
                    context.tbl_DDH_Hangs,
                    g => g.id_Hang,
                    h => h.id_Hang,
                    (g, h) => new
                    {
                        id_NhaCungCap = g.id_NhaCungCap,
                        id_Hang = g.id_Hang,
                        id_DonDatHang = h.id_DonDatHang,
                        TenNhaCungCap = g.TenNhaCungCap
                    }
                ).Join(
                    context.tbl_DonDatHangs,
                    i => i.id_DonDatHang,
                    j => j.id_DonDatHang,
                    (i, j) => new
                    {
                        id_NhaCungCap = i.id_NhaCungCap,
                        id_DonDatHang = i.id_DonDatHang,
                        id_NhanVien = j.id_NhanVien,
                        NgayDat = j.NgayDat,
                        NgayGiao = j.NgayGiao,
                        TenNhaCungCap = i.TenNhaCungCap
                    }
                ).Join(
                    context.tbl_NhanViens,
                    l => l.id_NhanVien,
                    m => m.id_NhanVien,
                    (l, m) => new 
                    {
                        id_NhaCungCap = l.id_NhaCungCap,
                        id_NhanVien = l.id_NhanVien,
                        TenNhanVien = m.TenNhanVien,
                        id_DonDatHang = l.id_DonDatHang,
                        TenNhaCungCap = l.TenNhaCungCap,
                        NgayDat = l.NgayDat,
                        NgayGiao = l.NgayGiao
                    }
                ).OrderByDescending(t => t.id_DonDatHang);
            return list;
        }

        public IQueryable HienThiDanhSachThemDDH(String id, String tenNCC)
        {
            var list = context.tbl_NhaCungCaps.Join(
                    context.tbl_NCC_Hangs,
                    a => a.id_NhaCungCap,
                    b => b.id_NhaCungCap,
                    (a, b) => new
                    {
                        id_NhaCungCap = a.id_NhaCungCap,
                        TenNhaCungCap = a.TenNhaCungCap,
                        id_Hang = b.id_Hang,
                    }
                ).Join(
                    context.tbl_Hangs,
                    c => c.id_Hang,
                    d => d.id_Hang,
                    (c, d) => new
                    {
                        id_NhaCungCap = c.id_NhaCungCap,
                        id_Hang = c.id_Hang,
                        TenNhaCungCap = c.TenNhaCungCap,
                        TenHang = d.TenHang
                    }
                ).Join(
                    context.tbl_DDH_Hangs,
                    g => g.id_Hang,
                    h => h.id_Hang,
                    (g, h) => new
                    {
                        id_NhaCungCap = g.id_NhaCungCap,
                        id_Hang = g.id_Hang,
                        TenNhaCungCap = g.TenNhaCungCap,
                        TenHang = g.TenHang,
                        SoLuong = h.SoLuong,
                        id_DonDatHang = h.id_DonDatHang
                    }
                ).Join(
                    context.tbl_DonDatHangs,
                    l => l.id_DonDatHang,
                    m => m.id_DonDatHang,
                    (l, m) => new 
                    {
                        id_NhaCungCap = l.id_NhaCungCap,
                        id_Hang = l.id_Hang,
                        id_DonDatHang = l.id_DonDatHang,
                        TenNhaCungCap = l.TenNhaCungCap,
                        TenHang = l.TenHang,
                        SoLuong = l.SoLuong,
                        NgayDat = m.NgayDat,
                        NgayGiao = m.NgayGiao
                    }
                ).Where(t => t.id_DonDatHang.Equals(id) && t.TenNhaCungCap.Equals(tenNCC));
            return list;
        }

        /// <summary>
        /// hien thi combobox hang
        /// </summary>
        /// <param name="id_NCC"></param>
        /// <returns></returns>
        public IQueryable getHangByNCC(String id_NCC)
        {
            var list = context.tbl_NCC_Hangs.Join(
                    context.tbl_Hangs,
                    a => a.id_Hang,
                    b => b.id_Hang,
                    (a, b) => new 
                    {
                        id_Hang = a.id_Hang,
                        id_NhaCungCap = a.id_NhaCungCap,
                        TenHang = b.TenHang
                    }
                ).Where(p => p.id_NhaCungCap.Equals(id_NCC));
            return list;
        }

        /// <summary>
        /// lau idNCC theo ten NCC
        /// </summary>
        /// <param name="tenNCC"></param>
        /// <returns></returns>
        public String GetIDNCCByTenNCC(String idNCC)
        {
            String a = context.tbl_NhaCungCaps.Where(p => p.id_NhaCungCap.Trim().Contains(idNCC.Trim())).Select(n => n.TenNhaCungCap).FirstOrDefault();
            return a;
        }

        /// <summary>
        /// lay idhang theo idNCC trong bang tbl_NCC_Hang
        /// </summary>
        /// <param name="idNCC"></param>
        /// <returns></returns>
        public List<String> getIDHangByNCC(String idNCC)
        {
            return context.tbl_NCC_Hangs.Where(p => p.id_NhaCungCap.Equals(idNCC)).Select(n => n.id_Hang).ToList();
        }

        //public List<tbl_Hang> getTenHangByIDDDH(String idddh)
        //{ 
        //    return 
        //}

    }
}
